package framework.database;

import java.util.Vector;

import org.apache.log4j.Category;

import framework.bean.Film;
import framework.bean.Reservation;
import framework.bean.User;
import framework.util.Util;
import framework.util.Value;


public class ServerDB {

  private static Category log = Category.getInstance(ServerDB.class.getName());
  private static DBAccess dBAccess = new DBAccess(Value.getProperty("source"));

  public static Vector checkDatabase() {
    String sql = "select 1 as ok";
    Vector vectRes = dBAccess.processSimpleQuery(sql);
    return vectRes;
  }

  public static Vector getUser(String login, String password) {
	String sql = "select * from utilisateur where uti_login=" + Util.protectStringSQL(login) + " and uti_password=" + Util.protectStringSQL(password);
	Vector vectRes = dBAccess.processSimpleQuery(sql);
	return vectRes;
  }
  /**
   * ajoute un utilisateur dans la base
   * @param usr User
   * @return id de la ligne ajouter ou -1 si erreur
   */
  public static int addUser(User usr){
	  String sql = "insert into utilisateur (uti_login,uti_password,uti_nom,uti_prenom,uti_email,uti_profil) " +
	  		"values ('"+usr.getLogin()+"','"+usr.getPassword()+"','"+usr.getNom()+"','"+usr.getPrenom()+"','"+usr.getEmail()+"',"+usr.getProfil()+")";
	  return dBAccess.processUpdate(sql);
  }
/**
 * ajoute un film dans la base
 * @param film Film
 * @return id de la ligne ajouter ou -1 si erreur
 */
  public static int addFilm(Film film){
	  String sql = "insert into film (film_titre, film_prix_reserv, film_qte) " +
	  		"values ('"+film.getFilm_titre()+"',"+film.getFilm_prix_reserv()+","+film.getFilm_qte()+")";
	  return dBAccess.processUpdate(sql);
  }
  /**
   * supprimer un film de la base
   * @param film Film
   * @return id ligne supprimer ou -1 si erreur
   */
  public static int suppFilm(Film film){
	  String sql = "delete from film where film_id ="+film.getFilm_id();
	  return dBAccess.processUpdate(sql);
  }
	public static int supprFilm(int id_film) {
		String sql = "update film set film_etat = 1 where film_id ="+id_film;
		  return dBAccess.processUpdate(sql);
	}
/**
* ajoute une reservation dans la base
* @param film Film
* @return id de la ligne ajouter ou -1 si erreur
*/
	public static int addReserv(Reservation reserv){
		  String sql = "insert into reservation (uti_id,film_id,reserv_date,reserv_date_fin,reserv_date_loc) " +
	"values ("+reserv.getCli_id()+","+reserv.getFilm_id()+","+"getdate()"/*reserv.getReserv_date()*/+","+
				  (reserv.getReserv_date_fin() == null? " ":"'")+ reserv.getReserv_date_fin()+
				  (reserv.getReserv_date_fin() == null? " ":"'")+","+(reserv.getReserv_date_loc() == null? "":"'")+
				  reserv.getReserv_date_loc()+(reserv.getReserv_date_loc() == null? "":"'")+")";
		  System.out.println(sql);
		  return dBAccess.processUpdate(sql);
	}
	
	public static Vector getAllFilm(){
		String sql = "select * from film where film_etat != 1";
		Vector v = dBAccess.processSimpleQuery(sql);
		if(v == null){
			v = new Vector();
		}
		log.debug(v);
		return v;
	}
	public static Vector getAllCli(){
		String sql = "select * from utilisateur where uti_etat != 1 and uti_profil = "+User.PROFIL_USER;
		Vector v = dBAccess.processSimpleQuery(sql);
		if(v == null){
			v = new Vector();
		}
		log.debug(v);
		return v;
	}

	public static int supprUser(int id_user) {
		String sql = "update utilisateur set uti_etat = 1 where uti_id ="+id_user;
		return dBAccess.processUpdate(sql);
	}

	public static Vector reservPerim(int heure){
		String sql = "select * from reservation where datediff(HH,getdate(),reserv_date) < -"+heure+" and reserv_annul = 0";
		Vector v = dBAccess.processSimpleQuery(sql);
		if(v == null){
			v = new Vector();
		}
		log.debug(v);
		return v;
	}

	public static Vector getReserv(int user_id) {
		String sql = "select * from reservation where reserv_annul = 0 and uti_id = "+user_id;
		Vector v = dBAccess.processSimpleQuery(sql);
		if(v == null){
			v = new Vector();
		}
		log.debug(v);
		return v;
	}
	  public static Vector getFilm(int film_id) {
			String sql = "select * from film where film_id="+film_id;
			Vector vectRes = dBAccess.processSimpleQuery(sql);
			return vectRes;
		  }

	public static int supprReserv(int id_reserv) {
		String sql = "update reservation set reserv_annul = 1 where reserv_id ="+id_reserv;
		return dBAccess.processUpdate(sql);
		
	}

	public static Vector getCli(int cli_id) {
		String sql = "select * from utilisateur where uti_id = "+cli_id ;
		Vector vectRes = dBAccess.processSimpleQuery(sql);
		return vectRes;
	}

	public static int loueReserv(int id_reserv) {
		String sql = "update reservation set reserv_date_loc = getdate() where reserv_id ="+id_reserv;
		return dBAccess.processUpdate(sql);
	}

	public static int renduReserv(int id_reserv) {
		String sql = "update reservation set reserv_date_fin = getdate() where reserv_id ="+id_reserv;
		return dBAccess.processUpdate(sql);
	}
  
}
